package com.tools.hry.xls.analysis.util;

import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;

/**
 * Created by 峥桂 on 2017/9/5
 */
public class ReadExcelUtils {
    //最小列数目
    final public  int MIN_ROW_COLUMN_COUNT = 1;

    //列索引
    private  int lastColumnIndex;
    //从Excel中读取的标题栏
    private  String[] headers;
    //从Excel中读取的数据
    private  String[][] datas;

    public static void main(String[] args) throws IOException, InvalidFormatException {
        ReadExcelUtils readExcelUtils= new ReadExcelUtils();
        readExcelUtils.readExcel(new FileInputStream("d:\\template.xls"),0,1,true);
        String[]  a=readExcelUtils.getHeaders();
        String[][]  b=readExcelUtils.getDatas();
        System.out.println(a.toString()+b.length);
    }

    /**
     * 读取Excel内容（从文件流）
     * @param ins
     * @param sheetIndex
     * @throws InvalidFormatException
     * @throws IOException
     */
    public  void readExcel(InputStream ins, int sheetIndex,int rowStart,boolean ignoreNullRow) throws InvalidFormatException, IOException {
//        _readExcel(StreamingReader.builder().bufferSize(1000).bufferSize(2048).open(ins), sheetIndex, rowStart);
//        _readExcel(StreamingReader.builder().bufferSize(10000).bufferSize(2048).open(ins), sheetIndex, rowStart);
        _readExcel(WorkbookFactory.create(ins), sheetIndex, rowStart,ignoreNullRow);
    }
    /**
     * 读取Excel内容
     * @param wb
     * @param sheetIndex
     */
    private  void _readExcel(Workbook wb, int sheetIndex,int rowStart,boolean ignoreNullRow) {
        // 遍历Excel Sheet， 依次读取里面的内容
        if (sheetIndex > wb.getNumberOfSheets()) {
            return;
        }
        Sheet sheet = wb.getSheetAt(sheetIndex);
        // 遍历表格的每一行
        // int rowStart = sheet.getFirstRowNum();

        if(ignoreNullRow){//是否忽略表头之前的空行
            if (rowStart <= 0) {
                rowStart = sheet.getFirstRowNum();
            }
        }

        // 最小行数为1行
        int rowEnd = sheet.getLastRowNum();
        // 读取EXCEL标题栏
        _parseExcelHeader(sheet.getRow(rowStart));
        // 读取EXCEL数据区域内容
        _parseExcelData(sheet, rowStart + 1, rowEnd + 1);
    }

    /**
     * 解析EXCEL标题栏
     * @param row
     */
    private  void _parseExcelHeader(Row row) {
        if(row==null){
            throw  new RuntimeException("当前解析的sheet页表头行位置存在问题，请检查！【所解析的行有空行】");
        }
        lastColumnIndex = Math.max(row.getLastCellNum(), MIN_ROW_COLUMN_COUNT);
        headers = new String[lastColumnIndex];
        // 初始化headers，每一列的标题
        for (int columnIndex = 0; columnIndex < lastColumnIndex; columnIndex++) {
            Cell cell = row.getCell(columnIndex, Row.RETURN_BLANK_AS_NULL);
            headers[columnIndex] = _getCellValue(cell).trim();
        }
    }
    /**
     * 读取每个单元格中的内容
     * @param cell
     * @return String
     */
    private  String _getCellValue(Cell cell) {
        // 如果单元格为空的，则返回空字符串
        if (cell == null) {
            return "";
        }
        // 根据单元格类型，以不同的方式读取单元格的值
        String value = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(cell.getDateCellValue());
                } else {
                    value = cell.getNumericCellValue() + "";
                }
                break;
            case Cell.CELL_TYPE_STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue() ? "TRUE" : "FALSE";
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            default:
        }
        return value;
    }


    /**
     * 解析EXCEL数据区域内容
     *
     * @param sheet
     * @param rowStart
     * @param rowEnd
     */
    private  void _parseExcelData(Sheet sheet, int rowStart, int rowEnd) {
        datas = new String[rowEnd - rowStart][lastColumnIndex];
        int data_row = 0;
        for (int rowIndex = rowStart; rowIndex < rowEnd; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            // 读取遍历每一行中的每一列
            String[] tempStrs=new String[lastColumnIndex];
            StringBuffer falgNullLine=new StringBuffer();
            for (int columnIndex = 0; columnIndex < lastColumnIndex; columnIndex++) {
                if(row==null){
                    continue;
                }
                Cell cell = row.getCell(columnIndex);
                String value = _getCellValue(cell).trim();
                if(value==null || value.length()==0){
                    continue;
                }
                falgNullLine.append(value);
                tempStrs[columnIndex]=value;
            }
            if(falgNullLine==null || falgNullLine.toString().length()==0){
                datas[data_row]=null;
            }else{
                datas[data_row]=tempStrs;
            }
            data_row++;
        }
    }

    public String[] getHeaders() {
        return headers;
    }

    public String[][] getDatas() {
        return datas;
    }

}
